from pyecharts.charts import Bar
from pyecharts import options as opts
from sc.ZDao import connect_mysql
import pymysql


def convert_salary(salary_str):
    """精确转换薪资格式为数值（单位：元）"""
    if not salary_str or salary_str == '面议':
        return None  # 不参与平均计算

    try:
        # 处理带薪数的格式（如1.5万·13薪）
        if '·' in salary_str and '薪' in salary_str:
            salary_str = salary_str.split('·')[0]

        if '万' in salary_str:
            if '-' in salary_str:
                min_sal, max_sal = salary_str.split('-')
                min_val = float(min_sal.replace('万', '')) * 10000
                max_val = float(max_sal.replace('万', '')) * 10000
                return (min_val + max_val) / 2  # 取范围中位数
            else:
                return float(salary_str.replace('万', '')) * 10000
        elif '千' in salary_str:
            if '-' in salary_str:
                min_sal, max_sal = salary_str.split('-')
                min_val = float(min_sal.replace('千', '')) * 1000
                max_val = float(max_sal.replace('千', '')) * 1000
                return (min_val + max_val) / 2
            else:
                return float(salary_str.replace('千', '')) * 1000
        elif 'k' in salary_str.lower():
            if '-' in salary_str:
                min_sal, max_sal = salary_str.split('-')
                min_val = float(min_sal.lower().replace('k', '')) * 1000
                max_val = float(max_sal.lower().replace('k', '')) * 1000
                return (min_val + max_val) / 2
            else:
                return float(salary_str.lower().replace('k', '')) * 1000
        elif '元' in salary_str:
            if '-' in salary_str:
                min_sal, max_sal = salary_str.split('-')
                return (float(min_sal.replace('元', '')) + float(max_sal.replace('元', ''))) / 2
            else:
                return float(salary_str.replace('元', ''))
        else:
            return float(salary_str) if salary_str.replace('.', '').isdigit() else None
    except:
        return None


def generate_accurate_salary_chart():
    # 连接数据库
    db = connect_mysql()
    if not db:
        print("数据库连接失败")
        return

    try:
        # 创建游标
        with db.cursor() as cur:
            # 查询所有Java岗位原始数据
            sql = """
            SELECT 
                job_name,
                job_sal,
                job_loc
            FROM jobs
            WHERE job_name LIKE '%Java%' OR job_name LIKE '%java%'
            ORDER BY job_loc
            """
            print("执行SQL:", sql)

            cur.execute(sql)
            results = cur.fetchall()

            # 按城市分组计算
            city_data = {}
            for row in results:
                city = row['job_loc'].split('·')[0].strip()
                salary = convert_salary(row['job_sal'])

                if salary is None:  # 跳过面议和无效数据
                    continue

                if city not in city_data:
                    city_data[city] = {
                        'total_salary': 0,
                        'count': 0,
                        'jobs': []
                    }

                city_data[city]['total_salary'] += salary
                city_data[city]['count'] += 1
                city_data[city]['jobs'].append({
                    'name': row['job_name'],
                    'salary': row['job_sal'],
                    'converted': salary
                })

            # 准备图表数据（按平均薪资排序）
            sorted_cities = sorted(
                city_data.items(),
                key=lambda x: x[1]['total_salary'] / x[1]['count'],
                reverse=True
            )[:8]  # 取前8个城市

            cities = []
            avg_salaries = []
            job_counts = []

            for city, data in sorted_cities:
                avg_salary = round(data['total_salary'] / data['count'])
                cities.append(city)
                avg_salaries.append(avg_salary)
                job_counts.append(data['count'])

                # 打印调试信息
                print(f"\n城市: {city}")
                print(f"平均薪资: {avg_salary}元 (共{data['count']}个有效岗位)")
                print("部分岗位示例:")
                for job in data['jobs'][:3]:  # 显示前3个岗位示例
                    print(f"  - {job['name']}: {job['salary']} => {job['converted']}元")

            # 创建专业柱状图（修复了bar_gap错误）
            bar = (
                Bar(init_opts=opts.InitOpts(width="1000px", height="600px"))
                .add_xaxis(cities)
                .add_yaxis(
                    "平均月薪(元)",
                    avg_salaries,
                    itemstyle_opts=opts.ItemStyleOpts(color="#5470C6"),
                    label_opts=opts.LabelOpts(
                        position="top",
                        formatter="{c}元",
                        font_size=14,
                        font_weight="bold",
                        color="#333"
                    ),
                    bar_width=50,
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最高"),
                            opts.MarkPointItem(type_="min", name="最低")
                        ]
                    )
                )
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title="各城市真实平均薪资对比",
                        subtitle="基于薪资范围中位数计算 | 数据量: {}个有效岗位".format(sum(job_counts)),
                        pos_left="center",
                        title_textstyle_opts=opts.TextStyleOpts(
                            font_size=22,
                            color="#333",
                            font_weight="bold"
                        ),
                        subtitle_textstyle_opts=opts.TextStyleOpts(
                            font_size=14,
                            color="#666"
                        )
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        trigger="axis",
                        axis_pointer_type="shadow",
                        formatter="城市: {b}<br/>平均薪资: {c}元<br/>岗位数量: {@[1]}",
                        extra_css_text="box-shadow: 0 0 10px rgba(0, 0, 0, 0.2);"
                    ),
                    xaxis_opts=opts.AxisOpts(
                        axislabel_opts=opts.LabelOpts(
                            rotate=45,
                            font_size=12,
                            color="#333",
                            margin=10
                        ),
                        axisline_opts=opts.AxisLineOpts(
                            linestyle_opts=opts.LineStyleOpts(width=2)
                        )
                    ),
                    yaxis_opts=opts.AxisOpts(
                        name="薪资(元)",
                        name_textstyle_opts=opts.TextStyleOpts(
                            font_size=14,
                            font_weight="bold"
                        ),
                        min_=0,
                        max_=max(avg_salaries) * 1.3 if avg_salaries else 30000,
                        axislabel_opts=opts.LabelOpts(
                            formatter="{value}元",
                            font_size=12
                        ),
                        splitline_opts=opts.SplitLineOpts(
                            is_show=True,
                            linestyle_opts=opts.LineStyleOpts(
                                type_="dashed",
                                opacity=0.3
                            )
                        )
                    ),
                    datazoom_opts=[opts.DataZoomOpts()],
                )
                .set_series_opts(
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    )
                )
            )

            # 添加岗位数量系列（使用extend_axis）
            bar.extend_axis(
                yaxis=opts.AxisOpts(
                    name="岗位数量",
                    type_="value",
                    min_=0,
                    max_=max(job_counts) * 1.5 if job_counts else 20,
                    axislabel_opts=opts.LabelOpts(formatter="{value}个"),
                    splitline_opts=opts.SplitLineOpts(is_show=False)
                )
            )

            bar.overlap(
                Bar()
                .add_xaxis(cities)
                .add_yaxis(
                    "岗位数量",
                    job_counts,
                    yaxis_index=1,
                    itemstyle_opts=opts.ItemStyleOpts(color="#d48265"),
                    label_opts=opts.LabelOpts(
                        position="top",
                        formatter="{c}个",
                        font_size=12
                    )
                )
            )

            # 渲染图表
            output_file = "baravg.html"
            bar.render(output_file)
            print(f"\n✅ 专业薪资对比图表已生成: {output_file}")

    except pymysql.Error as e:
        print(f"数据库操作出错: {e}")
    finally:
        if db:
            db.close()


if __name__ == "__main__":
    generate_accurate_salary_chart()